<?php

// copyright (c) 2010 Bouncing Ltd, ShopUpgraded
// licence Modified FreeBSD
// http://shopupgraded.com/sites/shopupgraded.com/files/freebsd.txt


require('includes/application_top.php');

header('Content-type: text/plain; charset=ISO-8859-1');

ini_set('log_errors', 1);          // store to file
ini_set('log_errors_max_len', 	0);  // unlimited length of message output
ini_set('display_errors', 1);
ini_set('error_reporting', E_ALL ^E_NOTICE && E_DEPRECATED);

if(!is_writeable(DIR_FS_SQL_CACHE)){
  die('The Folder '.DIR_FS_SQL_CACHE. ' needs to be writeable.' );
}

$db->execute('SET NAMES latin1');
$db->execute('SET CHARACTER SET latin1');

$fp = fopen(DIR_FS_SQL_CACHE.'/su_export_data.sql', 'w+');



$sql = "SELECT configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, NOW() as last_modified, date_added, use_function, set_function FROM ".TABLE_CONFIGURATION."  WHERE configuration_key NOT IN ('STORE_PAGE_PARSE_TIME_LOG', 'SESSION_WRITE_DIRECTORY', 'LPS_DIRECTORY', 'EASYPOPULATE_CONFIG_TEMP_DIR', 'AMAZON_DIRECTORY', 'AMAZON_IL_DIRECTORY', 'EBID_DIRECTORY', 'EBAY_DIRECTORY', 'YAHOO_DIRECTORY', 'SITEMAPXML_PING_URLS', 'GOOGLE_FROOGLE_DIRECTORY', 'IH_VERSION' )";

$query = $db->execute($sql);

$str = '
# upgrade to ShopUpgraded

# configuration data dump

';

print "Exporting configuration data
";

fwrite($fp, $str);

$i=0;

while(!$query->EOF){

  foreach($query->fields as $key=>$value){
    $query->fields[$key] = str_replace("\'", "'", str_replace("'", "''", $value ));
  }

  $str = "INSERT INTO configuration (".join(", ", array_keys($query->fields)).") VALUES ('".join("', '", $query->fields)."') ON DUPLICATE KEY UPDATE configuration_value='".str_replace("'", "''", $query->fields['configuration_value'])."' ;\r\n" ;

  print '~'; flush();

  if($i==80){
    print "\r\n";
    flush();
    $i=0;
  }else{
    $i++;
  }



fwrite($fp, $str);


  $query->MoveNext();
}

print "
";

$straight_table = array(

TABLE_ADDRESS_BOOK,
TABLE_ADDRESS_FORMAT,
TABLE_AUTHORIZENET,
TABLE_CATEGORIES,
TABLE_CATEGORIES_DESCRIPTION,
TABLE_COUNTRIES,
TABLE_COUPON_GV_QUEUE,
TABLE_COUPON_GV_CUSTOMER,
TABLE_COUPON_EMAIL_TRACK,
TABLE_COUPON_REDEEM_TRACK,
TABLE_COUPON_RESTRICT,
TABLE_COUPONS,
TABLE_COUPONS_DESCRIPTION,
TABLE_CURRENCIES,
TABLE_CUSTOMERS,
TABLE_CUSTOMERS_BASKET,
TABLE_CUSTOMERS_BASKET_ATTRIBUTES,
TABLE_CUSTOMERS_INFO,
TABLE_EMAIL_ARCHIVE,
TABLE_EZPAGES,
TABLE_FEATURED,
TABLE_FILES_UPLOADED,
TABLE_GROUP_PRICING,
TABLE_GET_TERMS_TO_FILTER,
TABLE_MANUFACTURERS,
TABLE_MANUFACTURERS_INFO,
TABLE_META_TAGS_PRODUCTS_DESCRIPTION,
TABLE_METATAGS_CATEGORIES_DESCRIPTION,
TABLE_NEWSLETTERS,
TABLE_NOCHEX,
TABLE_NOCHEX_SESSION,
TABLE_ORDERS_PRODUCTS,
TABLE_ORDERS_PRODUCTS_ATTRIBUTES,
TABLE_ORDERS_PRODUCTS_DOWNLOAD,
TABLE_ORDERS_STATUS,
TABLE_ORDERS_STATUS_HISTORY,
TABLE_ORDERS_TOTAL,
TABLE_PAYPAL,
TABLE_PAYPAL_SESSION,
TABLE_PAYPAL_PAYMENT_STATUS,
TABLE_PAYPAL_PAYMENT_STATUS_HISTORY,
TABLE_PAYPAL_TESTING,
TABLE_PRODUCT_TYPES,
TABLE_PRODUCT_TYPE_LAYOUT,
TABLE_PRODUCT_TYPES_TO_CATEGORY,
TABLE_PRODUCTS_ATTRIBUTES,
TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD,
TABLE_PRODUCTS_DESCRIPTION,
TABLE_PRODUCTS_DISCOUNT_QUANTITY,
TABLE_PRODUCTS_NOTIFICATIONS,
TABLE_PRODUCTS_OPTIONS,
TABLE_PRODUCTS_OPTIONS_VALUES,
TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS,
TABLE_PRODUCTS_OPTIONS_TYPES,
TABLE_PRODUCTS_TO_CATEGORIES,
TABLE_QUERY_BUILDER,
TABLE_REVIEWS,
TABLE_REVIEWS_DESCRIPTION,
TABLE_SALEMAKER_SALES,
TABLE_SPECIALS,
TABLE_TAX_CLASS,
TABLE_TAX_RATES,
TABLE_GEO_ZONES,
TABLE_ZONES_TO_GEO_ZONES,
TABLE_WISHLIST,
TABLE_WHOS_ONLINE,
TABLE_ZONES,
TABLE_RECORD_ARTISTS,
TABLE_RECORD_ARTISTS_INFO,
TABLE_RECORD_COMPANY,
TABLE_RECORD_COMPANY_INFO,
TABLE_PRODUCT_MUSIC_EXTRA,
TABLE_MUSIC_GENRE,
TABLE_MEDIA_MANAGER,
TABLE_MEDIA_TYPES,
TABLE_MEDIA_CLIPS,
TABLE_MEDIA_TO_PRODUCTS,
'google_checkout',
'google_orders',
TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK,
TABLE_PRODUCTS_XSELL,
TABLE_CUSTOMERS_ADMIN_NOTES,
TABLE_SO_PAYMENTS,
TABLE_SO_PAYMENT_TYPES,
TABLE_SO_REFUNDS

);

foreach($straight_table as $const=>$tbl){


$sql = "SHOW TABLES LIKE '".$tbl."'";

$query = $db->execute($sql);

  if($query->RecordCount()>0){

    $sql = "SELECT * FROM ".$tbl." ";

print "
Exporting data for $tbl
";

  $str = "

  # ".str_replace(DB_PREFIX, '', $tbl)." data dump

  TRUNCATE TABLE ".str_replace(DB_PREFIX, '', $tbl)." ;

";

  fwrite($fp, $str);


  $query = $db->execute($sql);

$i = 0;

  while(!$query->EOF){

    foreach($query->fields as $key=>$value){
      $query->fields[$key] = str_replace("\'", "'", str_replace("'", "''", $value ));
    }

    $str = "INSERT INTO ".str_replace(DB_PREFIX, '', $tbl)." (".join(", ", array_keys($query->fields)).") VALUES ('".join("', '", $query->fields)."') ;\r\n" ;

  print '~'; flush();

  if($i==80){
    print "\r\n";
    flush();
    $i=0;
  }else{
    $i++;
  }

    fwrite($fp, $str);

    $query->MoveNext();
  }

  }else{


  $str = "

--TABLE '".$const."' => XXXXX $tbl does not exist

";

fwrite($fp, $str);


  }

}




$str = "\r\nDROP TABLE orders;\r\n";

fwrite($fp, $str);



$sql = "SHOW CREATE TABLE ".TABLE_ORDERS."";

$query = $db->execute($sql);

$str = $query->fields['Create Table'].";\r\n\r\n";

$created = $str;

fwrite($fp, preg_replace('/'.TABLE_ORDERS.'/', 'orders', $str) );



$sql = "SELECT * FROM ".TABLE_ORDERS." ";

$query = $db->execute($sql);

$i = 0;

  while(!$query->EOF){

    foreach($query->fields as $key=>$value){
      $query->fields[$key] = str_replace("\'", "'", str_replace("'", "''", $value ));
    }

    $str = "INSERT INTO orders (".join(", ", array_keys($query->fields)).") VALUES ('".join("', '", $query->fields)."') ;\r\n" ;

  print '~'; flush();

  if($i==80){
    print "\r\n";
    flush();
    $i=0;
  }else{
    $i++;
  }

    fwrite($fp, $str);

    $query->MoveNext();
  }

$str = "ALTER TABLE orders CONVERT TO CHARACTER SET latin1;
ALTER TABLE orders ADD COLUMN inv_details MEDIUMBLOB DEFAULT null;
\r\n";

fwrite($fp, $str);

// check if cowoa was already installed.

if(!preg_match('/cowoa/si', $created)){

$str = "ALTER TABLE orders ADD COLUMN COWOA_order tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE orders ADD COLUMN dropdown varchar(50) DEFAULT NULL;
ALTER TABLE orders ADD COLUMN gift_message varchar(100) DEFAULT NULL;
ALTER TABLE orders ADD COLUMN checkbox int(1) DEFAULT NULL;

";


fwrite($fp, $str);

}


// products


$str = "\r\nDROP TABLE products;\r\n";

fwrite($fp, $str);



$sql = "SHOW CREATE TABLE ".TABLE_PRODUCTS."";

$query = $db->execute($sql);

$str = $query->fields['Create Table'].";\r\n\r\n";

$created = $str;

fwrite($fp, preg_replace('/'.TABLE_PRODUCTS.'/', 'products', $str) );



$sql = "SELECT * FROM ".TABLE_PRODUCTS." ";

$query = $db->execute($sql);

$i = 0;

  while(!$query->EOF){

    foreach($query->fields as $key=>$value){
      $query->fields[$key] = str_replace("\'", "'", str_replace("'", "''", $value ));
    }

    $str = "INSERT INTO products (".join(", ", array_keys($query->fields)).") VALUES ('".join("', '", $query->fields)."') ;\r\n" ;

  print '~'; flush();

  if($i==80){
    print "\r\n";
    flush();
    $i=0;
  }else{
    $i++;
  }

    fwrite($fp, $str);

    $query->MoveNext();
  }

$str = "
ALTER TABLE products CONVERT TO CHARACTER SET latin1;
\r\n";

fwrite($fp, $str);

// check if numinix product fields were already installed.

if(!preg_match('/products_upc/si', $created)){

$str = "ALTER TABLE products ADD COLUMN products_upc varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_isbn varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_ean varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_asin varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_epier varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_ebay varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_ebid varchar(32) DEFAULT NULL;

ALTER TABLE products ADD COLUMN products_actual_weight varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_weight_type enum('lbs','kgs') NOT NULL DEFAULT 'lbs';
ALTER TABLE products ADD COLUMN products_dim_type enum('in','cm') NOT NULL DEFAULT 'in';
ALTER TABLE products ADD COLUMN products_length decimal(6,2) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_width decimal(6,2) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_height decimal(6,2) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_ready_to_ship enum('0','1') NOT NULL DEFAULT '1';

ALTER TABLE products ADD COLUMN products_sh_na varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_sh_sa varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_sh_eu varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_sh_af varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_sh_as varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_sh_au varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_category varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_condition varchar(32) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_EHF varchar(100) NOT NULL DEFAULT 'none';
ALTER TABLE products ADD COLUMN products_barcode varchar(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN products_diameter varchar(32) DEFAULT NULL;

";


fwrite($fp, $str);

}

$str = "

TRUNCATE TABLE TABLE seo_cache;

UPDATE configuration SET configuration_value='true', configuration_group_id='6' WHERE configuration_key='SEO_ADD_CPATH_TO_PRODUCT_URLS';


UPDATE countries SET countries_name = 'Serbia', countries_iso_code_2 = 'RS', countries_iso_code_3 = 'SRB' where countries_iso_code_3 = 'YUG';
UPDATE countries SET countries_name = 'Timor-Leste', countries_iso_code_2 = 'TL', countries_iso_code_3 = 'TLS' where countries_iso_code_3 = 'TMP';
UPDATE countries SET countries_name = 'Moldova' where countries_iso_code_3 = 'MDA';
UPDATE countries SET countries_name = 'Macao' where countries_iso_code_3 = 'MAC';
UPDATE countries SET countries_iso_code_3 = 'ROU' where countries_iso_code_3 = 'ROM';

ALTER TABLE authorizenet CHANGE transaction_id transaction_id bigint default NULL;
ALTER TABLE paypal MODIFY COLUMN notify_version varchar(6) NOT NULL default '';

REPLACE INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show Split Tax Lines', 'SHOW_SPLIT_TAX_CHECKOUT', 'false', 'If multiple tax rates apply, show each rate as a separate line at checkout', '1', '22', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());

ALTER TABLE orders_products ADD INDEX idx_prod_id_orders_id_zen (products_id,orders_id);
ALTER TABLE orders ADD INDEX idx_cust_id_orders_id_zen (customers_id,orders_id);


INSERT INTO project_version (project_version_id, project_version_key, project_version_major, project_version_minor, project_version_patch1, project_version_patch1_source, project_version_patch2, project_version_patch2_source, project_version_comment, project_version_date_applied) VALUES ('', 'Zen-Cart Main', '1', '3.9a', '', '', '', '', 'Fresh Installation', now());
-- INSERT INTO project_version (project_version_id, project_version_key, project_version_major, project_version_minor, project_version_patch1, project_version_patch1_source, project_version_patch2, project_version_patch2_source, project_version_comment, project_version_date_applied) VALUES (2, 'Zen-Cart Database', '1', '3.9a', '', '', '', '', 'Fresh Installation', now());
-- INSERT INTO project_version_history (project_version_id, project_version_key, project_version_major, project_version_minor, project_version_patch, project_version_comment, project_version_date_applied) VALUES (1, 'Zen-Cart Main', '1', '3.9a', '', 'Fresh Installation', now());
INSERT INTO project_version_history (project_version_id, project_version_key, project_version_major, project_version_minor, project_version_patch, project_version_comment, project_version_date_applied) VALUES ('', 'Zen-Cart Database', '1', '3.9a', '', 'Fresh Installation', now());



";

fwrite($fp, $str);